Open the example file you downloaded (from here).
You will see that the database has only two tables it in now, one for Customers, and one for Orders.We are going to add a third table, but first we need to covers a few points about tables.
If you click on the Customer table you will see it looks a lot like an excel sheet filled with data.
You can see there is data for 29 customers in our database. For each customer we have a set of columns that contain some attribute of a customer - their first or last name, the company they work for, their phone number, and so on. Each row of data is called a record, and the structure of a record is the same for every customer in the table.
In some ways this view is exactly the same as using excel: you can click on any of the cells to add or change the data, and you can click on a new line at the bottom and add a new record, or you could delete a line. The difference is that excel will let you enter anything in any cell. If you put text in a number field it might break something (a formula, for example), but excel will happily let you do it anyway. Access, on the other hand, won't let you put data where it doesn't belong.
In excel, the location (which cell it is in) of data is how you know what it is and how you manipulate it. To write a formula to add two pieces of data (two numbers) together, you look up which cells the numbers are in and write the formula to add them together (e.g. "=A1+A2"), and you put that in some other cell (location). It doesn't matter where you decided to put the data originally, but if you move them later the formula will break. You (the operator) also need to make sure that there are only numbers in the cells you want to use for this formula, because excel won't care - if you accidentally put "twelve" in one of those cells by mistake excel isn't going to stop you trying to add it to another number, it will just give you a failed result.
You can also put lots of different types of data one one "page" (sheet), in whatever free-form design you prefer - you can make your excel worksheets look like christmas trees if you feel like it - because you as the user can make sense of it; excel doesn't have to. Usually though, you want to be able to deal with a lot of data (that's the advantage of computers after all) - to add together a long column of numbers, or perhaps create a filter based on thousands of rows. To do that you generally end up needing data in List Data format - every column contains the same type of data, no empty rows of data and so on - because excel features like autofilters, conditional highlights, and pivot tables only work over data organized that way. That format is basically emulating a database record. In a database, each piece of data is defined, and how each piece goes with other pieces is captured in the record structure.
You can often use either tool (spreadsheet or database) for the same job, but the following distinction might help clarify the best uses of each:
Getting back to our example, the elements of a database table you need to remember are as follows:
* The number of records in a table is conceptually unlimited, but very large databases can exceed the limits of different products (Microsoft Access is a consumer / small business database product meant for desktop use, for instance, while Microsoft SQL Server is an enterprise database product). Access 2016 limits include a 2GB file size, and 255 fields per table.
For every field in a table, we need to define the datatype of that data. This type defines what sort of data can be held in that field, and enables certain processing. Let's examine the design strucure of the Orders table below. To see this yourself, click on the "View" button on the left hand side of the menu ribbon.
Looking at the Order table, we can see that the OrderID field is a Number (more on that in a moment), the Order Date is Date/Time, and the Shipping Fee is a Currency. This means that only numbers can be entered in the OrderId field, only valid dates can be entered in the Order Date field, and data entered into the Shipping Fee field must correspond to rules about currency. The following table from the Access Help shows you the usage for each of the datatypes built in to Access.
An idea we need to introduce at this point is that of a key.
We need to be able to tell one record from another. We do this by making one of the fields (or one combination of fields) in a table the primary key. Primary key status means:
If we need more than one field to comply with the rules above we call that a compound key (made of multiple fields). For instance, people names aren't very unique (try getting a gmail account using your full name and no numbers in it), but a person's name plus DOB might be unique in some situations. So a compound primary key might be Name+DOB. If you do this, be very sure you can comply with all the rules above, especially the uniqueness requirement.
In our file, CustomerID and OrderID are the primary keys of the Customer and Order tables, respectively. You can tell by the little key icon next to the field name in the design view. You can't have a table without a primary key defined - Access will not let you save the table.
If you look at Northwind, many of the key fields have the type "autonumber". This means Access will assign the number automatically, ensuring it remains unique. If you want to manage the ID numbers yourself, you can just use Number.
The other reason we need a key, is so that we can link records from different tables together (we will look at that soon).
We now have all we need to create a new table. We are going to add the table Shippers. This table will hold data on who the shipping agent is for all our orders.
To create the table, go to the Create Menu (top ribbon menu), and click on Table.
You will see a new table, in datasheet view. By default, Access gives it the name Table1, until you save it. Do that now, by right clicking on the Table tab, and clicking "save". Call the new table Shippers.
Now click to change to the table design view. You will see Access wants you to have a primary key field, so by default it gives you the field "ID" with the type autonumber, and the little primary icon is present. Just to make things very clear, we are going to rename ID and call it ShipperID. Click on the field and edit the name. You can leave the datatype as autonumber.
Now we want to add all the other fields to our table. If this was a database you were designing, you would need to think about which fields you need and what types they should have, but we are going to follow the Northwind example, so add fields until your table matches the image below.
We now have a table that has all of the fields defined, but its empty, waiting for data. You can type this in now (I chose Shippers because it only has 3 records). Note that the Northwind creators were fairly slack by the time they got to this table, so most of the fields are empty and all 3 shippers seem to have the same address.... If this was a production system, those would be red flags for bad data.
Now that we have our new table, it is time to connect it to our other tables. Continue on to table relationships.